package db;

import extraction.html.chase.AccountSummary;
import extraction.html.chase.AccountTransaction;

import java.sql.*;

/**
 * @author Nathan Petryk (nathanp@indeed.com)
 */
public class ChaseDAO {

    private JDBCTemplate jdbc;

    public ChaseDAO(String sqlUrl, String uid, String upass) throws SQLException {
        jdbc = new JDBCTemplate(
                DriverManager.getConnection(sqlUrl, uid, upass)
        );
    }

    public AccountSummary getAccountById(int id) {

        AccountSummary rtn;
        String sql = "SELECT * FROM accounts WHERE accnt_id="+id;

        try {
            ResultSet rs = jdbc.select( sql );
            rs.next();
            rtn = new AccountSummary(
                    rs.getInt("accnt_id"),
                    rs.getString("accnt_name"),
                    rs.getInt("accnt_lst4"),
                    rs.getDouble("pres_balance"),
                    rs.getDouble("avail_balance"),
                    rs.getString("trans_url")
            );

            if( rs.next() )
                throw new DatabaseSanityException("SELECT FROM accounts WHERE accnt_id="+id+" yielded more than one result!");

            return rtn;
        } catch(SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public int insertAccount(AccountSummary accnt) throws SQLException {
        try {
            return jdbc.insert(
                    "INSERT INTO accounts " +
                      "(accnt_name, accnt_lst4, pres_balance, avail_balance, trans_url) " +
                      "VALUES(?,?,?,?,?)",
                    accnt.accountName,
                    accnt.lastFourDigits,
                    accnt.presentBalance,
                    accnt.availableBalance,
                    accnt.transactionURL
            );
        } catch(SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }

    public int insertTransaction(AccountTransaction trnsctn, int accntId) throws SQLException {
        try {
            return jdbc.insert(
                    "INSERT INTO transactions " +
                    "(accnt_id, trans_date, trans_type, trans_desc, trans_debit, trans_credit, accnt_balance) " +
                    "VALUES(?, ?, ?, ?, ?, ?, ?);",
                    accntId,
                    trnsctn.date,
                    trnsctn.type,
                    trnsctn.description,
                    trnsctn.debit,
                    trnsctn.credit,
                    trnsctn.balance
            );
        } catch(SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }



    

    

    public static void main(String[] args) throws Exception {

        ChaseDAO cdao = new ChaseDAO("jdbc:mysql://localhost/pers", "persuser", "1234");
                    /*
        AccountSummary sum = new AccountSummary(
                -1,
        );
                      */
        AccountSummary summary = cdao.getAccountById(19);
        System.out.println(summary.accountName);
    }
}
